Merging and Summarizing Data

October 27, 2023

Merging Data Frames

  • Often we have data from two different sources
  • Results in two data frames
  • How to make them one so we can analyze?
  • Key questions
    • What is the unit of analysis?
    • What is/are the corresponding identifier variables?
    • Are the identifier variables in common?
    • Or do they have to be added/transformed to match?

Merging WB and V-Dem Data


  • These are both time-series, country-level data
  • Need to merge by country-year
  • Year is easy
  • But there are many different country codes
  • Can use countrycode package to assign country codes

countrycode example


# Load countrycode
library(countrycode)

# Create new iso3c variable
democracy <- democracy |>    
  mutate(iso3c = countrycode(sourcevar = vdem_ctry_id, # what we are converting
        origin = "vdem",         # we are converting from vdem
        destination = "wb"))  |> # and converting to the WB iso3c code 
  relocate(iso3c, .after = vdem_ctry_id) # move iso3c 

# View the data
glimpse(democracy)

Try it Yourself


  • Using your democracy data frame from the last lesson
  • Use mutate() and countrycode() to add iso3c country codes
  • Use relocate to move your iso3c code to the “front” of your data frame (optional)
05:00

Types of joins in dplyr

  • Mutating versus filtering joins
  • Four types of mutating joins
    • inner_join()
    • full_join()
    • left_join()
    • right_join()
  • For the most part we will use left_join()

left_join() example


# Load readr
library(readr)

# Perform left join using common iso3c variable and year
dem_women <- left_join(democracy, women_emp, by = c("iso3c", "year")) |> 
  rename(country = country.x) |> # rename country.x
  select(!country.y)             # crop country.y

# Save as .csv for future use
write_csv(dem_women, "data/dem_women.csv")

# View the data
glimpse(dem_women)

Try it Yourself


  • Take your V-Dem data frame and your World Bank data frame
  • Using left_join() to merge on country code and year
  • Along the way, use rename() and select() to insure you have just one country name
05:00

Group, Summarize and Arrange


  • group_by(), summarize(), arrange()
  • A very common sequence in data science:
    • Take an average or some other statistic for a group
    • Rank from high to low values of summary value

Example


# group_by(), summarize() and arrange()
dem_summary <- dem_women |> # save result as new object
  group_by(region)  |> # group dem_women data by region
  summarize(           # summarize following vars (by region)
    polyarchy = mean(polyarchy, na.rm = TRUE), # calculate mean, remove NAs
    gdp_pc = mean(gdp_pc, na.rm = TRUE), 
    flfp = mean(flfp, na.rm = TRUE), 
    women_rep = mean(women_rep, na.rm = TRUE)
  ) |> 
  arrange(desc(polyarchy)) # arrange in descending order by polyarchy score

# Save as .csv for future use
write_csv(dem_summary, "data/dem_summary.csv")

# View the data
glimpse(dem_summary)

Try it Yourself

  • Try running a group_by(), summarize() and arrange() in your Quarto document
  • Try changing the parameters to answer these questions:
  1. Try summarizing the data with a different function for one or more of the variables.
  1. What is the median value of polyarchy for The West?
  2. What is the max value of gdp_pc for Eastern Europe?
  3. What is the standard deviation of flfp for Africa?
  4. What is the interquartile range of women_rep for the Middle East?
  1. Now try grouping by country instead of region.
  1. What is the median value of polyarchy for Sweden?
  2. What is the max value of gdp_pc New Zealand?
  3. What is the standard deviation of flfp for Spain?
  4. What is the interquartile range of women_rep for Germany?

  1. Sort countries in descending order based on the mean value of gdp_pc (instead of the median value of polyarchy). Which country ranks first based on this sorting?

  2. Now try sorting countries in ascending order based on the median value of women_rep (hint: delete “desc” from the arrange() call). Which country ranks at the “top” of the list?

Coding Assignment 1


Coding assignment 1 builds off this lesson


Take the remainder of the class time to work on it. I will be here in case you have questions.


You may work individually or in groups.

DataViz 6502 Website